import json

import pymysql
from itemadapter import ItemAdapter
from Knowledge_Planet.items import *
from datetime import datetime
import re

class MySQLPipeline:
    def __init__(self, host, port, user, password, db):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db = db
        self.fp1 = None

    @classmethod
    def from_crawler(cls, crawler):
        return cls(
            host=crawler.settings.get('MYSQL_HOST'),
            port=crawler.settings.get('MYSQL_PORT'),
            user=crawler.settings.get('MYSQL_USER'),
            password=crawler.settings.get('MYSQL_PASSWORD'),
            db=crawler.settings.get('MYSQL_DB')
        )

    def open_spider(self, spider):
        self.fp1 = open("topic_id.txt", "a", encoding="utf-8")  # 用作记录,作为判定程序结束的标识
        self.conn = pymysql.connect(
            host=self.host,
            port=self.port,
            user=self.user,
            password=self.password,
            db=self.db,
            charset='utf8mb4',
            cursorclass=pymysql.cursors.DictCursor
        )

    def process_item(self, item, spider):
        if item:
            self.fp1.write(str(item['topic_id']) + "|" + str(item['create_time']) + "\n")
            # if "talk" in res_dic.keys():
            #     if 'files' in res_dic['talk'].keys():
            #         file_list = res_dic['talk']['files']~
            #         for file_dic in file_list:
            #             file_id = file_dic['file_id']
            #             file_name = file_dic['name']
        self.fp1.flush()
        if isinstance(item, InitialPageItem):
            self.insert_initial_page_item(item)

        # 最新｜往期实战
        # if isinstance(item, ActivityItem):
        #     self.insert_activity_item(item)
        # # 高手分享
        # elif isinstance(item, PracticalDetailsVeteranItem):
        #     self.insert_practical_details_veteran_item(item)
        # # 百问百答
        # elif isinstance(item, PracticalDetailsDiscussItem):
        #     self.insert_practical_details_discuss_item(item)
        # # 首页主页
        # elif isinstance(item, InitialPageItem):
        #     self.insert_initial_page_item(item)
        # # 项目库
        # elif isinstance(item, ProjectRepositoryItem):
        #     self.insert_project_repository_item(item)
        # # 项目库topic字典表
        # elif isinstance(item, ProjectRepositoryTopicItem):
        #     self.insert_project_repository_topic_item(item)
        # # 项目库 article字典表
        # elif isinstance(item, ProjectRepositoryProjectItem):
        #     self.insert_project_repository_article_item(item)
        # # 项目库详情页
        # elif isinstance(item, ProjectRepositoryDetailsItem):
        #     self.insert_project_repository_details_item(item)
        return item



    def insert_initial_page_item(self, item):

        adapter = ItemAdapter(item)
        create_time_iso = adapter['create_time']
        time = re.sub('T', ' ', create_time_iso)[:19]
        create_time = datetime.strptime(time,  "%Y-%m-%d %H:%M:%S")
        pass
        # content_create_time = adapter['content_create_time']
        # content_modify_time = adapter['content_modify_time']
        # if '' == adapter['content_create_time']:
        #     content_create_time = '1900-01-01 00:00:00'
        # else:
        #     content_create_time = datetime.strptime(content_create_time, '%Y-%m-%dT%H:%M:%S.%f%z').strftime(
        #         '%Y-%m-%d %H:%M:%S')
        #
        # if not adapter['content_modify_time']:
        #     content_modify_time = '1900-01-01 00:00:00'
        # else:
        #     content_modify_time = datetime.strptime(content_modify_time, '%Y-%m-%dT%H:%M:%S.%f%z').strftime(
        #         '%Y-%m-%d %H:%M:%S')

        # # 将包含字典的数据转换为字符串形式
        # user_specific_str = json.dumps(adapter['user_specific'])
        # user_specific_str_escaped = pymysql.escape_string(user_specific_str)

        data = {
            '`create_time`': create_time,
            '`topic_id`': adapter['topic_id'],
            '`group_info`': json.dumps(adapter['group_info']),
            '`content_type`': adapter['content_type'],
            '`talk_owner`': json.dumps(adapter['talk_owner']),
            '`talk_text`': adapter['talk_text'],
            '`talk_images`': json.dumps(adapter['talk_images']),
            '`latest_likes`': json.dumps(adapter['latest_likes']),
            '`show_comments`': json.dumps(adapter['show_comments']),
            '`likes_count`': adapter['likes_count'],
            '`rewards_count`': adapter['rewards_count'],
            '`comments_count`': adapter['comments_count'],
            '`digested`': adapter['digested'],
            '`sticky`': adapter['sticky'],
            '`user_specific`': json.dumps(adapter['user_specific']),
            '`hashtags`': json.dumps(adapter['hashtags']),
            '`uid`': adapter['uid'],
            '`className`': adapter['className'],
            '`reading_count`': adapter['reading_count'],
            '`readers_count`': adapter['readers_count'],
            '`talk_files`': json.dumps(adapter['talk_files']),
            '`user_headimg`': adapter['user_headimg']

        }

        # print(data)

        fields = ', '.join(data.keys())

        placeholders = ', '.join(['%({})s'.format(field) for field in data.keys()])

        query = f"INSERT INTO crawl_home_page_query_article ({fields}) VALUES ({placeholders})"

        try:
            with self.conn.cursor() as cursor:
                cursor.execute(query, data)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e


    def insert_project_repository_item(self, item):

        adapter = ItemAdapter(item)

        data = {
            'content_count': json.dumps(adapter['content_count']),
            'hot': json.dumps(adapter['hot']),
            'hot_words': json.dumps(adapter['hot_words']),
            'project': json.dumps(adapter['project']),
            'toolbar': json.dumps(adapter['toolbar']),
            'topic': json.dumps(adapter['topic']),

        }
        print(data)

        fields = ', '.join(data.keys())

        placeholders = ', '.join(['%({})s'.format(field) for field in data.keys()])

        query = f"INSERT INTO rawl_project_repository ({fields}) VALUES ({placeholders})"

        try:
            with self.conn.cursor() as cursor:
                cursor.execute(query, data)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e


    def insert_project_repository_topic_item(self, item):

        adapter = ItemAdapter(item)

        data = {
            'topic_label': adapter['topic_label'],
            'topic_value': adapter['topic_value'],
            'label': adapter['label'],
            'name': adapter['name'],
            'value': adapter['value'],

        }
        print(data)

        fields = ', '.join(data.keys())

        placeholders = ', '.join(['%({})s'.format(field) for field in data.keys()])

        query = f"INSERT INTO rawl_project_repository_topic ({fields}) VALUES ({placeholders})"

        try:
            with self.conn.cursor() as cursor:
                cursor.execute(query, data)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e



    def insert_project_repository_article_item(self, item):

        adapter = ItemAdapter(item)

        data = {
            'project_article_cnt': adapter['project_article_cnt'],
            'project_category_cnt': adapter['project_category_cnt'],
            'project_name': adapter['project_name'],
            'project_cnt': adapter['project_cnt'],
            'category_article_cnt': adapter['category_article_cnt'],
            'category_name': adapter['category_name'],
            'category_project_cnt': adapter['category_project_cnt'],
            'article_id': adapter['article_id'],
            'article_name': adapter['article_name'],
            'article_cnt': adapter['article_cnt'],

        }
        print(data)

        fields = ', '.join(data.keys())

        placeholders = ', '.join(['%({})s'.format(field) for field in data.keys()])

        query = f"INSERT INTO rawl_project_repository_article ({fields}) VALUES ({placeholders})"

        try:
            with self.conn.cursor() as cursor:
                cursor.execute(query, data)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e



    def insert_project_repository_details_item(self, item):

        adapter = ItemAdapter(item)

        data = {
            'menu_id': adapter.get('menu_id'),
            'article_content': adapter.get('article_content'),
            'comments_count': adapter.get('comments_count'),
            'create_user_id': adapter.get('create_user_id'),
            'create_user_level': adapter.get('create_user_level'),
            'show_create_user_id': adapter.get('show_create_user_id'),
            'show_title': adapter.get('show_title'),
            'gmt_create': adapter.get('gmt_create'),
            'gmt_update': adapter.get('gmt_update'),
            'is_digested': adapter.get('is_digested'),
            'like_count': adapter.get('like_count'),
            'rewards_count': adapter.get('rewards_count'),
            'reading_count': adapter.get('reading_count'),
            'menu_ids': adapter.get('menu_ids'),
            'type': adapter.get('type'),
            'task_topic_id': adapter.get('task_topic_id'),
            'topic_id': adapter.get('topic_id'),
            'menu_info': json.dumps(adapter['menu_info']),
            'user_info': json.dumps(adapter['user_info'])

        }
        print(data)

        fields = ', '.join(data.keys())

        placeholders = ', '.join(['%({})s'.format(field) for field in data.keys()])

        query = f"INSERT INTO rawl_project_repository_details ({fields}) VALUES ({placeholders})"

        try:
            with self.conn.cursor() as cursor:
                cursor.execute(query, data)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e





    def insert_activity_item(self, item):

        adapter = ItemAdapter(item)

        data = {
            'id': adapter['id'],
            'gmt_create': adapter['gmt_create'],
            'name': adapter['name'],
            'label': adapter['label'],
            'cover_img': adapter['cover_img'],
            'raw_name': adapter['raw_name'],
            'template': json.dumps(adapter['template']),
            'template_raw_name': adapter['template_raw_name'],
            'template_avatar': adapter['template_avatar'],
            'template_platform': adapter['template_platform'],
            'template_target': adapter['template_target'],
            'template_refund_num': adapter['template_refund_num'],
            'join_cnt': adapter['join_cnt'],
            'user_avatar': adapter['user_avatar'],
            'article_tag': adapter['article_tag'],
            'gmt_start': adapter['gmt_start'],
            'gmt_0': adapter['gmt_0'],
            'gmt_1': adapter['gmt_1'],
            'gmt_end': adapter['gmt_end'],
            'price': adapter['price'],
            'menu': json.dumps(adapter['menu']),
            'is_refund': adapter['is_refund'],
        }
        print(data)

        fields = ', '.join(data.keys())

        placeholders = ', '.join(['%({})s'.format(field) for field in data.keys()])

        query = f"INSERT INTO crawl_practical_exercises_activity ({fields}) VALUES ({placeholders})"

        try:
            with self.conn.cursor() as cursor:
                cursor.execute(query, data)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e



    def insert_practical_details_veteran_item(self, item):

        adapter = ItemAdapter(item)

        data = {
            'id': adapter['id'],
            'gmt_create': adapter['gmt_create'],
            'activity_id': adapter['activity_id'],
            'activity_name': adapter['activity_name'],
            'activity_label': adapter['activity_label'],
            'category': adapter['category'],
            'title': adapter['title'],
            'tag': json.dumps(adapter['tag']),
            'href': adapter['href'],
            'content': adapter['content'],
            'extra': json.dumps(adapter['extra']),
            'is_delete': adapter['is_delete']
        }
        print(data)

        fields = ', '.join(data.keys())

        placeholders = ', '.join(['%({})s'.format(field) for field in data.keys()])

        query = f"INSERT INTO crawl_practical_details_veteran ({fields}) VALUES ({placeholders})"
        print(query)

        try:
            with self.conn.cursor() as cursor:
                cursor.execute(query, data)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e


    def insert_practical_details_discuss_item(self, item):

        adapter = ItemAdapter(item)
        createdAt_iso = adapter['createdAt']
        updatedAt_iso = adapter['updatedAt']
        createdAt = datetime.strptime(createdAt_iso, '%Y-%m-%dT%H:%M:%S.%fZ').strftime('%Y-%m-%d %H:%M:%S')
        updatedAt = datetime.strptime(updatedAt_iso, '%Y-%m-%dT%H:%M:%S.%fZ').strftime('%Y-%m-%d %H:%M:%S')
        data = {
            'objectId': adapter['objectId'],
            'tag': json.dumps(adapter['tag']),
            'extra': json.dumps(adapter['extra']),
            'qid': adapter['qid'],
            'gmt_create': adapter['gmt_create'],
            'category': adapter['category'],
            'title': adapter['title'],
            'href': adapter['href'],
            'content': adapter['content'],
            'activity_id': adapter['activity_id'],
            'createdAt': createdAt,
            'updatedAt': updatedAt,
            'like_list': json.dumps(adapter['like_list']),
            'elastic': adapter['elastic'],
            'ACL': json.dumps(adapter['ACL'])
        }
        print(data)

        fields = ', '.join(data.keys())

        placeholders = ', '.join(['%({})s'.format(field) for field in data.keys()])

        query = f"INSERT INTO crawl_practical_details_discuss ({fields}) VALUES ({placeholders})"

        try:
            with self.conn.cursor() as cursor:
                cursor.execute(query, data)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise e




    def close_spider(self, spider):
        self.fp1.close()
        self.conn.close()

